import datetime
import glob
import os.path
import sqlite3
import collections
from pathlib import Path

import numpy as np

from StressAna.Lib.DataAna import AnkeDataAna
from StressAna.Lib.Model import Globalvariables
from StressAna.Lib.Utils import DateTimeUtil, DrawUtil


class SQLiteAPI(object):
    def __init__(self, dbname=None):
        self.dbname = dbname if dbname is not None else '../../Data/database.db'
        self.conn = sqlite3.connect(self.dbname)  # 如果文件不存在，会自动在当前目录创建:
        # 创建一个Cursor
        self.cursor = self.conn.cursor()

    def create(self, table: str, fields: dict):
        '''
        建表（待完善）
        :param table: 表名
        :param fields: 字段及类型
        :return: 执行结果
        '''
        flag = True
        try:
            data = fields.items()
            func = lambda x: ' '.join(x)
            field_info = ','.join(list(map(func, data)))
            # create table fields
            sql_create = f"CREATE TABLE {table} ({field_info})"
            print(sql_create)
            self.cursor.execute(sql_create)
            self.conn.commit()  # 提交事务
        except Exception as e:
            print(e)
            flag = False
        return flag

    def insert(self, table: str, data: dict):
        '''
        插入语句
        :param table: 表名
        :param data: 字典（包含插入列，值）
        :return: 执行结果
        '''
        flag = True
        count = 0
        try:
            data = collections.OrderedDict(data)
            keys = tuple(data.keys())
            vals = tuple(data.values())
            sql_insert = '''INSERT INTO %s %s VALUES %s''' % (table, keys, vals)
            # print(sql_insert)
            self.cursor.execute(sql_insert)
            count = self.cursor.rowcount
            self.conn.commit()
        except Exception as e:
            print(e)
            flag = False
        return flag, count

    def select(self, table: str, cols='*', where=''):
        '''
        查询
        :param table: 表名
        :param cols: 查询列
        :param where: 查询条件
        :return: 查询结果
        '''
        values = None
        try:
            where = 'WHERE ' + where if where else ''
            sql_select = '''SELECT {1} FROM {0} {2}'''.format(table, cols, where)
            # print(sql_select)
            self.cursor.execute(sql_select)
            values = self.cursor.fetchall()
        except Exception as e:
            print(e)

        return values

    def select_by_sql(self, sql):
        '''
        查询
        :param table: 表名
        :param cols: 查询列
        :param where: 查询条件
        :return: 查询结果
        '''
        values = None
        try:
            self.cursor.execute(sql)
            values = self.cursor.fetchall()
        except Exception as e:
            print(e)

        return values

    def update(self, table: str, data: dict, where=''):
        '''
        更新表
        :param table: 表名
        :param data:更新数据
        :param where:条件
        :return: 执行结果
        '''
        flag = True
        count = 0
        try:
            data = data.items()
            func = lambda x: ' = '.join(x)
            field_info = ','.join(list(map(func, data)))
            where = ' WHERE ' + where if where else ''
            sql_update = '''UPDATE {0} SET {1} {2}'''.format(table, field_info, where)
            # sql_update = '''UPDATE USER SET NAME = 'sun' WHERE ID = 3 '''   # test
            # print(sql_update)
            self.cursor.execute(sql_update)
            count = self.cursor.rowcount
            self.conn.commit()
        except Exception as e:
            print(e)
            flag = False
        return flag, count

    def delete(self, table: str, where=''):
        '''
        删除数据
        :param table: 表名
        :param where: 条件
        :return: 执行结果
        '''
        flag = True
        count = 0
        if not where:
            print('条件不能为空，请重新尝试！')
            return False, count
        try:
            sql_delete = '''DELETE FROM {0} WHERE {1}'''.format(table, where)

            self.cursor.execute(sql_delete)
            count = self.cursor.rowcount
            self.conn.commit()
        except Exception as e:
            print(e)
            flag = False
        return flag, count

    def drop(self, table: str):
        '''
        删除表
        :param table:
        :return:
        '''
        flag = True
        try:
            sql_drop = f'''DROP TABLE {table}'''
            self.cursor.execute(sql_drop)
            self.conn.commit()
        except Exception as e:
            print(e)
            flag = False
        return flag

    def __del__(self):
        self.cursor.close()  # 关闭Cursor
        self.conn.close()  # 关闭Connection


def update_code():
    conn = SQLiteAPI(db_path)
    for i, sta in enumerate(Globalvariables.STATION_dict):
        code, channel = sta.split('-')
        result = conn.select('device_info', '*', f"code='{code}' and channel='{channel}'")
        if result:
            continue
        data_dict = {'code': code, 'nid': Globalvariables.nid_list[i], 'channel': channel,
                     'length': Globalvariables.STATION_CD[i], 'wall_thick': Globalvariables.STATION_BH[i],
                     'hole_depth': Globalvariables.STATION_KS[i], 'initial_pressure': Globalvariables.STATION_CSY[i]}
        conn.insert('device_info', data_dict)


def update_td_data():
    conn = SQLiteAPI(db_path)
    file_list = [r'D:\LK\TMV煤矿项目\应力数据\obsdata\1\G01\G01_2024-02-01.txt',
                 r'D:\LK\TMV煤矿项目\应力数据\obsdata\1\G02\G02_2024-02-01.txt']
    for file in glob.glob(r'D:\LK\TMV煤矿项目\应力数据\obsdata\1\**\*.txt', recursive=True):
        # for file in file_list:
        print(file)
        _name = Path(file).name[:3]
        _date = Path(file).name[4:14]
        sql = f"SELECT s.device_id FROM stress_data s inner join device_info d on s.device_id=d.id WHERE d.code='{_name}' and s.date='{_date}'"
        if conn.select_by_sql(sql):
            continue
        with open(file, 'r') as f:
            lines = f.read().splitlines()
        lines_list = [x.split(',') for x in lines[1:]]
        raw_data = np.array(lines_list).transpose(1, 0)

        for chn in [1, 2]:
            date_time_list = ','.join(raw_data[0])
            value_list = ','.join(raw_data[int(chn)])
            if device_id := conn.select('device_info', 'id', f"code='{_name}' and channel={chn}"):
                data_dict = {'device_id': device_id[0][0], 'date': _date, 'date_time': date_time_list,
                             'value': value_list}
                conn.insert('stress_data', data_dict)


def update_sk_data():
    conn = SQLiteAPI(db_path)
    # file_list = [r'D:\LK\Project\data-plot\StressAna\Data\钻孔应力传感器-10-_20240125143622.xlsx',
    #              r'D:\LK\Project\data-plot\StressAna\Data\钻孔应力传感器-11-_20240125143639.xlsx']
    # for file in file_list:
    for file in glob.glob(r'D:\LK\TMV煤矿项目\应力数据\AK\思科赛德数据\**\*.xlsx', recursive=True):
        print(file)

        ak_data = AnkeDataAna.AnkeAna(file)
        df = ak_data.df
        for sid, dfi in df.groupby('sID'):
            if not conn.select('device_info', '*', f"code='{sid}'"):
                for chn in ['1', '2']:
                    data_dict = {'code': sid, 'nid': sid, 'channel': chn,
                                 'length': '', 'wall_thick': '', 'hole_depth': '', 'initial_pressure': ''}
                    conn.insert('device_info', data_dict)

        df['date'] = df['time'].apply(lambda x: str(x)[:10])
        df['time'] = df['time'].apply(lambda x: str(x))
        df['ch1'] = df['ch1'].apply(lambda x: str(x))
        df['ch2'] = df['ch2'].apply(lambda x: str(x))

        for _date, dfi in df.groupby('date'):
            _name = dfi.iloc[0]['sID']
            sql = f"SELECT s.device_id FROM stress_data s inner join device_info d on s.device_id=d.id WHERE d.code='{_name}' and s.date='{_date}'"
            if conn.select_by_sql(sql):
                continue
            date_time_list = ','.join(dfi['time'].to_list())
            for chn in [1, 2]:
                value_list = ','.join(dfi[f'ch{chn}'].to_list())
                if device_id := conn.select('device_info', 'id', f"code='{_name}' and channel={chn}"):
                    data_dict = {'device_id': device_id[0][0], 'date': _date, 'date_time': date_time_list,
                                 'value': value_list}
                    conn.insert('stress_data', data_dict)


def _sel():
    conn = SQLiteAPI(db_path)
    _sid_list = ['G01-1', 'G02-1', 'G03-1', 'G04-1', '10-2', '11-2']
    start_datetime = '2024-01-20 00:00:00'
    end_datetime = '2024-01-21 00:00:00'
    start_date = DateTimeUtil.formatDateTimeBy(DateTimeUtil.parseDateTimeBy(start_datetime, "%Y-%m-%d %H:%M:%S"),
                                               '%Y-%m-%d')
    end_date = DateTimeUtil.formatDateTimeBy(DateTimeUtil.parseDateTimeBy(end_datetime, "%Y-%m-%d %H:%M:%S"),
                                             '%Y-%m-%d')
    date_list = DateTimeUtil.get_date_str_list_by_date_range(start_date, end_date, '%Y-%m-%d')
    _date = ','.join([f"'{x}'" for x in date_list])
    data_list = []
    for sid in _sid_list:
        iname, ichn = sid.split('-')
        sql = f"SELECT d.code, d.channel, s.date_time, s.value FROM stress_data s inner join device_info d on s.device_id=d.id WHERE d.code='{iname}' and d.channel='{ichn}' and s.date in ({_date})"
        result = conn.select_by_sql(sql)
        srcX = []
        srcY = []
        dstX = []
        dstY = []
        for row in result:
            row_date_time_list = row[2].split(',')
            row_value_list = row[3].split(',')
            srcX.extend(row_date_time_list)
            srcY.extend(row_value_list)
        for i, date_time in enumerate(srcX):
            if start_datetime <= date_time <= end_datetime:
                dstX.append(date_time)
                dstY.append(srcY[i])
        X = [datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S") for x in dstX]
        Y = [float(x) for x in dstY]
        data_list.append((X, Y))
    DrawUtil.draw_util(data_list, labels=_sid_list)


if __name__ == '__main__':
    db_path = '../../Data/database.db'
    # update_data()
    # _sel()

    # update_sk_data()
    _sel()
